CREATE TEMPORARY TABLE orders_delta AS
SELECT orderid,
from_utc_timestamp(date_format(orderdate,'yyyy-MM-dd HH:mm:ss.SSS'),'UTC') AS order_date,
datediff(orderdate, lag(orderdate) OVER (order by orderid)) AS delta
FROM orders
ORDER BY delta DESC;
SELECT MAX(delta) AS max_delta FROM orders_delta;
SELECT * FROM orders_delta WHERE delta IN (SELECT MAX(delta) AS delta FROM orders_delta);
USE flights;
SELECT DISTINCT(f.airline) as hnl_airlines, a.airline AS hnl_airline_name FROM flights f
JOIN airlines a
ON a.iata_code = f.airline
WHERE destination_airport = 'HNL';
SELECT DISTINCT SUBSTRING(LPAD(scheduled_departure,4,"0"),1,2) AS hour
FROM flights f WHERE f.destination_airport='HNL' and f.origin_airport='SFO';
SELECT f.day_of_week, f.airline, a.airline, AVG(f.departure_delay) AS avg_delay
FROM flights f
JOIN airlines a
ON a.iata_code = f.airline
WHERE f.destination_airport = 'HNL'
GROUP BY f.day_of_week, f.airline, a.airline
ORDER BY avg_delay ASC;
SELECT f.destination_airport, a.airport, COUNT(f.destination_airport) AS total_llegadas
FROM flights f
JOIN airports a
ON a.iata_code = f.destination_airport
GROUP BY f.destination_airport, a.airport
ORDER BY total_llegadas DESC LIMIT 10;
#### 2.e ¿Cuál es la aerolínea con mayor retraso de salida por día de la semana?
SELECT f.airline, a.airline, f.day_of_week, f.departure_delay FROM flights f
JOIN (
SELECT day_of_week, MAX(departure_delay) AS maximo FROM flights
GROUP BY day_of_week
) ff
ON f.departure_delay = ff.maximo
JOIN airlines a
ON f.airline = a.iata_code
ORDER BY f.day_of_week;
SELECT f.airline, a.airline, min(f.departure_delay) AS menor
FROM flights f
JOIN airlines a
ON a.iata_code = f.airline
WHERE day_of_week = 2
GROUP BY f.airline, a.airline
ORDER BY menor ASC
LIMIT 3;
SELECT a.airport, COUNT(DISTINCT f.destination_airport) AS num_destinos
FROM flights f
JOIN airports a
ON a.iata_code = f.origin_airport
GROUP BY a.airport
ORDER BY num_destinos DESC
LIMIT 1;